﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using FFS.DBUtility;
using FFS.IDAL;
using FFS.Model;

namespace FFS.SQLDAL
{
    public class FamilyMemberDAO:IFamilyMemberDAO
    {
        private const string FAMILY_MEMBER_ID = "@FamilyMemberID";
        private const string FAMILY_MEMBER_NAME = "@FamilyMemberName";
        private const string FAMILY_MEMBER_TITLE = "@FamilyMemberTitle";
        private const string INPUT_USER = "@input_user";
        private const string SELECT_ALL_FAMILY_MEMBER = "select FamilyMemberID,FamilyMemberName,FamilyMemberTitle,input_date,input_user from FamilyMember where is_deleted = 0 ";
        private const string INSERT_FAMILY_MEMBER = @" INSERT INTO FamilyMember(FamilyMemberName,FamilyMemberTitle,is_deleted,input_date,input_user)
                                                            values (@FamilyMemberName,@FamilyMemberTitle,0,getdate(),@input_user)";
        private const string UPDATE_FAMILY_MEMBER = @" UPDATE FamilyMember 
                                                         set  FamilyMemberName = @FamilyMemberName ,
                                                              FamilyMemberTitle = @FamilyMemberTitle,
                                                              input_date = getdate(),
                                                              input_user = @input_user 
                                                        where FamilyMemberID = @FamilyMemberID and is_deleted = 0 ";
        private const string DELETE_FAMILY_MEMBER = @"  UPDATE FamilyMember 
                                                            set is_deleted = 1,input_date = getdate(),input_user = @input_user 
                                                         where is_deleted = 0 and FamilyMemberID = @FamilyMemberID 
                                                      ";

        private List<FamilyMemberInfo> GetQueryList(CommandType cmdType,string sqlText,params SqlParameter[] param)
        {
            List<FamilyMemberInfo> fm_list = new List<FamilyMemberInfo>();

            using (SqlDataReader reader = DBHelper.ExecuteReader(cmdType,sqlText,param))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        FamilyMemberInfo familyMember = new FamilyMemberInfo();
                        familyMember.FamilyMemberID = reader.GetInt32(0);
                        familyMember.FamilyMemberName = reader.GetString(1);
                        familyMember.FamilyMemberTitle = reader.GetString(2);
                        familyMember.Input_Date = reader.GetDateTime(3);
                        familyMember.Input_User = reader.GetString(4);
                        fm_list.Add(familyMember);
                    }
                }
            }

            return fm_list;
        }

        #region IFamilyMemberDAO 成员

        public List<FamilyMemberInfo> GetAllFamilyMember()
        {
            return GetQueryList(CommandType.Text,SELECT_ALL_FAMILY_MEMBER,null);
        }

        public List<FamilyMemberInfo> GetFamilyMemberByMemberName(string memberName)
        {
            SqlParameter param = new SqlParameter(FAMILY_MEMBER_NAME,memberName);
            string sqlText = " and FamilyMemberName = @FamilyMemberName ";
            return GetQueryList(CommandType.Text,sqlText,param);
        }

        public List<FamilyMemberInfo> GetFamilyMemberBySearchCondition(string searchCondition)
        {
            string sqlText = SELECT_ALL_FAMILY_MEMBER + " and " + searchCondition;
            return GetQueryList(CommandType.Text,sqlText,null);
        }

        public int CreateFamilyMember(FamilyMemberInfo member)
        {
            SqlParameter[] param = {
                                        new SqlParameter(FAMILY_MEMBER_NAME,member.FamilyMemberName),
                                        new SqlParameter(FAMILY_MEMBER_TITLE,member.FamilyMemberTitle),
                                        new SqlParameter(INPUT_USER,"0000")
                                   };
            return DBHelper.ExecuteNonQuery(CommandType.Text,INSERT_FAMILY_MEMBER,param);
        }

        public int ModifyFamilyMember(FamilyMemberInfo member)
        {
            //SqlParameter[] param = new SqlParameter[4];
            //param[0].Value = new SqlParameter(FAMILY_MEMBER_NAME,member.FamilyMemberName);
            //param[1].Value = new SqlParameter(FAMILY_MEMBER_TITLE,member.FamilyMemberTitle);
            //param[2].Value = new SqlParameter(INPUT_USER, member.Input_User);
            //param[3].Value = new SqlParameter(FAMILY_MEMBER_ID, member.FamilyMemberID);
            SqlParameter[] param = { 
                                    new SqlParameter(FAMILY_MEMBER_ID,member.FamilyMemberID),
                                    new SqlParameter(FAMILY_MEMBER_NAME,member.FamilyMemberName),
                                    new SqlParameter(FAMILY_MEMBER_TITLE,member.FamilyMemberTitle),
                                    new SqlParameter(INPUT_USER,member.Input_User)
                                   };

            return DBHelper.ExecuteNonQuery(CommandType.Text,UPDATE_FAMILY_MEMBER,param);
        }

        public int DeleteFamilyMember(int familyMemberID)
        {
            SqlParameter[] param = {
                                    new SqlParameter(FAMILY_MEMBER_ID,familyMemberID),
                                    new SqlParameter(INPUT_USER,"0000")
                                   };

            return DBHelper.ExecuteNonQuery(CommandType.Text,DELETE_FAMILY_MEMBER,param);
        }

        public bool FamilyMemberIsExists(string familyMemberName)
        {
            string sqlText = SELECT_ALL_FAMILY_MEMBER + " and FamilyMemberName = @FamilyMemberName ";

            SqlParameter param = new SqlParameter(FAMILY_MEMBER_ID,familyMemberName);

            if(DBHelper.ExecuteScalar(CommandType.Text,sqlText,param) != null)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion
    }
}
